USE Travian

IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Players'
              AND TYPE = 'v')
DROP VIEW Players
GO
CREATE VIEW Players
AS
Select DISTINCT x.uid as PID, 
x.player as Name,
x.tid as Tribe, 
(Select SUM(y.Population) from x_world y where y.player = x.player) as Pop, 
x.alliance as Alliance from x_world x;
GO

IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Alliances'
              AND TYPE = 'v')
DROP VIEW Alliances
GO
CREATE VIEW Alliances
AS
SELECT DISTINCT  x.aid AS AID ,x.alliance as Name from x_world x;
GO

IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Alliance_Stats'
              AND TYPE = 'v')
DROP VIEW Alliance_Stats
GO
CREATE VIEW Alliance_Stats
AS
Select a.Name ,Count(a.AID) Members,AVG(p.Pop) Pop,MAX(p.Pop) as 'Top', MIN(p.Pop) as 'Min' from Alliances a,Players p where a.Name = P.Alliance GROUP BY a.Name;
GO
